package com.apestech.api.query;

import com.apestech.framework.db.SqlResultSet;

import java.io.*;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

import com.apestech.framework.db.SqlSession;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.json.JSON;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.FileUtil;
import com.apestech.framework.util.LocalVariable;
import com.apestech.framework.util.StringUtil;
import com.apestech.framework.util.callback.SpiltStrCallback;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipOutputStream;

/**
 * XLS导出查询
 *
 * @author hup
 * @create 2018-05-29-16:23
 */
public class XLSQuery {

    public enum XlsType {
        FILE(0), DATA_ARRAY(1);
        int value;
        XlsType(int value) {
            this.value = value;
        }
        public int getValue() {
            return value;
        }
    }

    private SqlResultSet rs;
    private XlsType type;
    private long maxSheetRow=20000;   //xlxs默认最大值为1048575，所在数据范围为0...1048575之间
    private long maxXlsRow=-1;   //xlxs文件默认最大行数
    private ArrayList<File> fileList = new ArrayList<File>();
    private String xlsName=null;
    private String dir;
    private String outFile;
    private String sheetName;
    private JSONArray head;
    private int colsize;
    private ArrayList<String> titlelist;
    private String[] fieldlist;

    public XLSQuery(SqlResultSet rs) throws Exception {
        this.dir= LocalVariable.getTempDir();
        iniRs(rs);
    }

    public XLSQuery(){
        this.dir= LocalVariable.getTempDir();
    }

    private void iniRs(SqlResultSet rs) throws Exception {
        this.rs=rs;
        this.type=XlsType.FILE;
        if(this.rs.getConParam()==null)
            this.head=this.rs.toHeadJSONArray();
        else {
            this.head=this.rs.getConParam().getHead();
            if(this.head==null) this.head=this.rs.toHeadJSONArray();
        }
        //if(this.rs.getConParam()!=null) this.head=this.rs.getConParam().getHead();
    }

    public XlsType getType() {
        return type;
    }

    public void setType(XlsType type) {
        this.type = type;
    }

    public void setMaxSheetRow(long maxSheetRow) {
        this.maxSheetRow = maxSheetRow;
    }

    public void setMaxXlsRow(long maxXlsRow) {
        this.maxXlsRow = maxXlsRow;
    }

    public void setXlsName(String xlsName) {
        this.xlsName = xlsName;
        if(this.xlsName==null){
            this.xlsName= StringUtil.getUUID();
        }
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public void setDir(String dir) {
        this.dir = dir;
    }

    public String getOutFile() {
        return this.outFile;
    }

    public void close() throws Exception {
        FileUtil.deleteDir(this.outFile);
    }

    public void setHead(JSONArray head){
        this.head=head;
        this.colsize=this.head.size();
    }

    public void exportXls(OutputStream os) throws Exception {
        if(this.type==XlsType.FILE){
            writeXls();
        } else {
            writeData(os);
        }
    }

    private void writeData(OutputStream os) throws Exception {
        getFieldList();
        if(os==null){
            os = new FileOutputStream(new File(LocalVariable.getTempDir()+"1.txt"));
        }
        os.write("\"content\":[".getBytes());
        int i=0;
        while(this.rs.next()){
            if(i==0){
                os.write(JSON.toJSONString(this.titlelist).getBytes("UTF-8"));
            }
            os.write(",".getBytes("UTF-8"));
            os.write(JSON.toJSONString(rs.toRowArray(this.fieldlist)).getBytes("UTF-8"));
            i++;
        }
        os.write("]".getBytes("UTF-8"));
    }

    private void getFieldList() throws Exception {
        String s="";
        this.titlelist= new ArrayList<String>();
        for(int i=0; i<colsize; i++) {
            JSONObject map= JSON.toJSON(this.head.get(i));
            String field=map.getString("field");
            String title=map.getString("title");
            this.titlelist.add(title);
            if(!"".equals(s)){
                s=s+";";
            }
            s=s+field;
        }
        if(colsize==1){
            s=s+";";
        }
        this.fieldlist=s.split(";");
    }

    public void writeXls() throws Exception {  //test
        int count=0;
        int order=0;
        try{
            XLSWrite xls=new XLSWrite(rs);
            xls.setHead(this.head);
            xls.setXlsName(this.xlsName);
            xls.setSheetName(this.sheetName);
            xls.setMaxSheetRow(this.maxSheetRow);
            xls.setMaxXlsRow(this.maxXlsRow);
            xls.setOrder(order);
            xls.writeXls();
            count=xls.getCount();
            this.fileList.add(new File(xls.saveToFile(this.dir, true)));
            while(xls.isMaxFile()){
                order++;
                xls=new XLSWrite(rs);
                xls.setHead(this.head);
                xls.setXlsName(this.xlsName);
                xls.setMaxSheetRow(this.maxSheetRow);
                xls.setMaxXlsRow(this.maxXlsRow);
                xls.setOrder(order);
                xls.writeXls();
                count=count+xls.getCount();
                this.fileList.add(new File(xls.saveToFile(this.dir, true)));
            }
            String fname=this.dir+this.xlsName+".rar";
            FileUtil.toZip(fileList, fname);
            outFile=fname;
            if(fileList.size()>=1){
                FileUtil.deleteDir(this.dir+this.xlsName);
            }
        } catch (Exception e){
            throw new Exception("写XLS失败"+e.toString());
        }
    }

    public void writeXlsAll(String sql, SqlSession session) throws Exception {
        final XLSWrite[] xls = {new XLSWrite()};
        final int[] order = {0};
        StringUtil.execSplitStr(sql, ";", new SpiltStrCallback() {
            @Override
            public void exec(String id) throws Exception {
                if(id!=null && !"".equals(id.trim())){
                    order[0]++;
                    session.getForResultSet().sql(id, new QueryCallBackHandler() {
                        @Override
                        public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                            iniRs(rs);
                            String xlsn=xlsName;
                            if(rs.getConParam()!=null && rs.getConParam().getFromtab()!=null){
                                xlsn=rs.getConParam().getFromtab().getTable(0);
                            }
                            xls[0].iniRs(rs);
                            xls[0].setHead(head);
                            xls[0].setXlsName(xlsn);
                            xls[0].setMaxSheetRow(maxSheetRow);
                            xls[0].setMaxXlsRow(maxXlsRow);
                            xls[0].setOrder(order[0]);
                            xls[0].writeXls();
                        }
                    });
                }
            }
        });
        xls[0].saveToFile(dir, false);

//        String url="smb://hujl:@10.2.4.210/net/";
//        SmbFile remoteF=new SmbFile(url+"test.xlsx");
//        remoteF.connect();
//        BufferedOutputStream out=new BufferedOutputStream(new SmbFileOutputStream(remoteF));
//        xls[0].saveToFile(out);
    }

}
